﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using GestorDeFlotas.Core;

namespace GestorDeFlotasDesktop.MejoresClientes
{
    public partial class MejoresClientes : Form
    {
        public MejoresClientes(int anio, int mesInicio, int mesFin)
        {
            InitializeComponent();
            this.Anio = anio;
            this.MesInicio = mesInicio;
            this.MesFin = mesFin;
        }

        private int Anio { get; set; }

        private int MesInicio { get; set; }

        private int MesFin { get; set; }

        private BindingSource bindingSource1 = new BindingSource();
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();

        private void btnCerrar_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void MejoresClientes_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = bindingSource1;
            GetData(@"SELECT TOP 5 C.cli_apellido AS Apellido, C.cli_nombre AS Nombre, SUM(F.fact_importeTotal) AS Importe
                      FROM [CRAZY_TAXI].[TL_Facturas] AS F
	                    INNER JOIN [CRAZY_TAXI].[TL_Clientes] AS C ON F.fact_cliente = C.cli_codigo
                    WHERE DATEPART(year,F.fact_fecha_fin) = " + Anio + " AND DATEPART(month,F.fact_fecha_fin) BETWEEN " + MesInicio + " AND " + MesFin +
                    @" GROUP BY F.fact_cliente, C.cli_apellido, C.cli_nombre
                    ORDER BY SUM(F.fact_importeTotal) DESC");
        }

        private void GetData(string selectCommand)
        {
            try
            {
                using (SqlConnection connection = SqlUtils.GetConnection())
                {
                    connection.Open();
                    // Create a new data adapter based on the specified query.
                    dataAdapter = new SqlDataAdapter(selectCommand, connection.ConnectionString);

                    // Populate a new data table and bind it to the BindingSource.
                    DataTable table = new DataTable();
                    table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                    dataAdapter.Fill(table);
                    bindingSource1.DataSource = table;

                    // Resize the DataGridView columns to fit the newly loaded content.
                    dataGridView1.AutoResizeColumns();
                }
            }
            catch (SqlException)
            {
                MessageBox.Show("Error al intentar obtener los datos de la base.");
            }
        }

    }
}
